package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.zr.gdms.dao.CourseDao;
import com.zr.gdms.util.JDBCUtil;

/**
 * @author  shichongming
 * @E-mail: 
 * @date 创建时间：2017年6月1日 下午7:23:00 
 * @version 1.0 
 * @parameter  
 * @since  
 * @return  
 */

public class CourseDaoImpl implements CourseDao{

	
	//展示所有课题信息----石铳铭
		//1,获取总数进行分页
	@Override
	public int getCountsCourse() {
		int total=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" select count(*) as total");
		sql.append(" from course");
	Connection con=JDBCUtil.getConnection();
	try {
		PreparedStatement pst=con.prepareStatement(sql.toString());
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			total=rs.getInt("total");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		return total;
	}
    //2,分页求数量
	@Override
	public JSONArray showAllCourse(int page,int rows) {
		JSONArray j=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" select course.cid,course.cname,course.cstate,course.tid,teacher.tname,teacher.tdepart,teacher.tprofe,teacher.ttel");
		sql.append(" from course");
		sql.append(" inner join teacher");
		sql.append(" on course.tid=teacher.tid");
		sql.append(" limit ?,?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1,page);
			pst.setInt(2, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){				
					JSONObject js=new JSONObject();
					js.put("cid", rs.getInt("cid"));
					js.put("cname", rs.getString("cname"));
					js.put("cstate", rs.getString("cstate"));
					js.put("tid", rs.getInt("tid"));
					js.put("tname", rs.getString("tname"));
					js.put("tdepart", rs.getString("tdepart"));
					js.put("tprofe", rs.getString("tprofe"));
					js.put("ttel", rs.getString("ttel"));	
					j.add(js);
			}						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return j;
	}
	
	
	
	
	//根据教师信息查询对应的课题
		//1.根据教师姓名信息求出相应的总数
	@Override
	public int getCountsCourseByTeacherInformation(String information) {
		int total=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" select count(*) as total");
		sql.append(" from course");
		sql.append(" inner join teacher");
		sql.append(" on course.tid=teacher.tid");
		sql.append(" where teacher.tname like ? or teacher.tdepart like ? or teacher.tprofe like ? or teacher.tage like ? or teacher.tsex like ? or teacher.ttel like ? ");
	Connection con=JDBCUtil.getConnection();
	try {
		PreparedStatement pst=con.prepareStatement(sql.toString());
		pst.setString(1,"%"+information+"%");
		pst.setString(2,"%"+information+"%");
		pst.setString(3,"%"+information+"%");
		pst.setString(4,"%"+information+"%");
		pst.setString(5,"%"+information+"%");
		pst.setString(6,"%"+information+"%");
		
		ResultSet rs=pst.executeQuery();	
		while(rs.next()){
			total=rs.getInt("total");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		return total;
	}
	
	//2.根据教师的信息进行分页查询
	@Override
	public JSONArray findCourseByTeacherInformation(String information, int page, int rows) {
		JSONArray j=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" select course.cid,course.cname,course.cstate,course.tid,teacher.tname,teacher.tdepart,teacher.tprofe,teacher.ttel");
		sql.append(" from course");
		sql.append(" inner join teacher");
		sql.append(" on course.tid=teacher.tid");
		sql.append(" where course.cstate like ? or course.cid like ? or course.tid like ? or course.cname like ? or teacher.tname like ? or teacher.tdepart like ? or teacher.tprofe like ?  limit ?,?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1,"%"+information+"%");
			pst.setString(2,"%"+information+"%");
			pst.setString(3,"%"+information+"%");
			pst.setString(4,"%"+information+"%");
			pst.setString(5,"%"+information+"%");
			pst.setString(6,"%"+information+"%");
			pst.setString(7,"%"+information+"%");		
			pst.setInt(8,page);
			pst.setInt(9,rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				JSONObject js=new JSONObject();
				js.put("cid", rs.getInt("cid"));
				js.put("cname", rs.getString("cname"));
				js.put("cstate", rs.getString("cstate"));
				js.put("tid", rs.getInt("tid"));
				js.put("tname", rs.getString("tname"));
				js.put("tdepart", rs.getString("tdepart"));
				js.put("tprofe", rs.getString("tprofe"));
				js.put("ttel", rs.getString("ttel"));	
				j.add(js);		
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return j;
	}
	
	
	
	
	//管理员修改课题信息----石铳铭
	@Override
	public boolean AdminUpdateCourseByCid(String cname, String cstate, int tid,int cid) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("");
		sql.append(" update  course set course.cname=?,course.cstate=?,course.tid=?");
		sql.append(" where course.cid=?");		
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1,cname);
			pst.setString(2, cstate);
			pst.setInt(3,tid);
			pst.setInt(4,cid);	
			int rs=pst.executeUpdate();
			if(rs>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	
	
	
	
	
	//展示待审核的课题
		//1.获取待审核的课题总数total
	@Override
	public int getWaitAuditingCourseTotal() {
		int total=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" select count(*) as total");
		sql.append(" from application");
	Connection con=JDBCUtil.getConnection();
	try {
		PreparedStatement pst=con.prepareStatement(sql.toString());
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			total=rs.getInt("total");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		return total;
	}
	//2.获取待审核课题的信息进行分页
	@Override
	public JSONArray showWaitAuditingCourse(String apptype,int page, int rows) {
		JSONArray j=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" select application.appid,application.appcontent,application.apptime,application.appstate,application.tid,teacher.tname,teacher.tdepart,teacher.tprofe,teacher.ttel");
		sql.append(" from application");
		sql.append(" INNER JOIN teacher");
		sql.append(" on application.tid=teacher.tid");
		sql.append(" where application.apptype=?");
		sql.append(" LIMIT ?,?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1,apptype);
			pst.setInt(2,page);
			pst.setInt(3, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){				
					JSONObject js=new JSONObject();
					js.put("appid", rs.getInt("appid"));
					js.put("appcontent", rs.getString("appcontent"));
					js.put("apptime", rs.getString("apptime"));
					js.put("appstate", rs.getString("appstate"));
					js.put("tname", rs.getString("tname"));
					js.put("tid", rs.getInt("tid"));
					js.put("tdepart", rs.getString("tdepart"));
					js.put("tprofe", rs.getString("tprofe"));
					js.put("ttel", rs.getString("ttel"));				
					j.add(js);
			}						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return j;
	}
	
	
	
	
	
	//管理员审核课题----石铳铭
	@Override
	public boolean auditingCourse(int appid) {
		 boolean flag=false;
		 StringBuffer sql=new StringBuffer("");
		 sql.append(" update application set application.appstate='false'");
		 sql.append(" where application.appid=?");
		 Connection con=JDBCUtil.getConnection();
		 try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1,appid);
			int rs=pst.executeUpdate();
			if(rs>0){
				flag=true;
			}								
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
		
	//将审核的课题信息新增到course表中
	@Override
	public boolean addAuditingCourse(String cname, String cstate, int tid) {
		boolean flag=false;
		 StringBuffer sql=new StringBuffer("");
		 sql.append(" INSERT  into  course(cname,cstate,tid) VALUES (?,?,?)");
	
		 Connection con=JDBCUtil.getConnection();
		 try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1,cname);
			pst.setString(2,cstate);
			pst.setInt(3,tid);
			int rs=pst.executeUpdate();
			if(rs>0){
				flag=true;
			}								
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	
	//删除待审核表中的flase状态的额课题(即已经审核成功的课题)
	@Override
	public boolean deleteAuditingCourse(int appid) {
		boolean flag=false;
		 StringBuffer sql=new StringBuffer("");
		 sql.append(" delete from application");
		 sql.append(" where application.appid=?");
		 Connection con=JDBCUtil.getConnection();
		 try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1,appid);			
			int rs=pst.executeUpdate();
			if(rs>0){
				flag=true;
			}								
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	
	
	
	
	
	
}
